package ext.db;


import java.lang.reflect.ParameterizedType;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.BeanUtils;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;

@SuppressWarnings("unchecked")
public abstract class Model<M,Id> extends ModelObject{
	
	private final SqlHelper sqlHelper;
	
	private boolean generarerId = true;

	public Model() {
		sqlHelper = new SqlHelper();
	}
	
	private M self(){
		return (M) this;
	}
	
	public M addTables(String...tables){
		for (int i = 0; i < tables.length; i++)
			add(","+tables[i]);
		return self();
	}
	
	public M selfTableAs(String name){
		add(" as "+name);
		return self();
	}
	
	public M addTables(Class<M>...modelClass){
		for (int i = 0; i < modelClass.length; i++)
			addTables(getModelTableName(modelClass[i]));
		return self();
	}
	
	@SuppressWarnings("rawtypes")
	public static <M> String getModelTableName(Class<M> modelClass){
		Model model = (Model) createModel(modelClass);
		return model.tableName();
	}
	
	/**
	 * @param name
	 * @param value
	 * @return
	 */
	public M set(String name,Object value){
		sqlHelper.add(name, value);
		return self();
	}
	
	
	/**
	 * 获取值
	 * @param name
	 * @return
	 */
	public <T> T get(String name){
		return (T) super.get(name);
	}
	
	/**
	 * 添加sql
	 * @param sql
	 * @return
	 */
	public M add(String sql){
		sqlHelper.getSqlBulider().append(sql);
		return self();
	}
	
	/**
	 * append sql 
	 * @param sql 
	 * @param params 
	 * @return
	 */
	public M add(String sql,Object...params){
		for (int i = 0; i < params.length; i++)
			sqlHelper.addParam(params[i]);
		return add(sql);
	}
	
	/**
	 * and 
	 * @return
	 */
	public M and(){
		return and("");
	}
	
	/**
	 * append sql and
	 * @param name
	 * @param params`
	 * @return
	 */
	public M and(String name,Object value){
		 return add(" and "+name +" = ?",value);
	}
	
	public M and(String sql){
		return add(" and "+sql);
	}
	
	public M and(String name,String conditions,Object value){
		 and();
		 conditions(name, conditions, value);
		 return self();
	}
	

	
	public M or(String name,Object value){
		 return add(" or "+name,value);
	}
	
	public M like(String name,String value){
		return conditions(name, "like", value);
	}
	
	public M eq(String name,Object value){
		return conditions(name, "=", value);
	}
	
	public M ne(String name,Object value){
		return conditions(name, "<>", value);
	}
		
	public M gt(String name,Object value){
		return conditions(name, ">", value);
	}
	
	public M lt(String name,Object value){
		return conditions(name, "<", value);
	}
	
	
	public M count(String name){
		column("count("+name+")");
		return (M) this;
	}

	public M where(){
		return where("");
	}
	
	public M where(String sql){
		return where(sql, new Object[]{});
	}
	
	public M where(String sql,Object...param){
		if(!sqlHelper.isUseConditions()){
			sqlHelper.append(" where ");
			sqlHelper.setUseConditions(true);
		}
		sqlHelper.append(sql);
		for (int i = 0; i < param.length; i++)
			sqlHelper.addParam(param[i]);
		return self();
	}
	
	public M column(String name){
		sqlHelper.addColumn(name);
		return self();
	}
	
	public M columns(String...columns){
		for (int i = 0; i < columns.length; i++)
			column(columns[i]);
		return self();
	}
	
	public M conditions(String name,String symbol,Object value){
		sqlHelper.getSqlBulider().append(name+" "+symbol+" ?");
		sqlHelper.addParam(value);
		return self();
	}
	
	public M orderBy(String str){
		return add(" order by "+str+" ");
	}
	
	public M orderByDesc(String prop){
		return orderBy(prop+" desc");
	}
	
	public M orderByAsc(String prop){
		return orderBy(prop+" asc");
	}
	
	public M groupBy(String name){
		sqlHelper.append(" group by "+name);
		return self();
	}
	
	public M having(String name){
		sqlHelper.append(" having "+name);
		return self();
	}
	
	/**
	 * @return
	 */
	public int total(){
		sqlHelper.getColumns().clear();	
		count("*");
		return unique(Integer.class);
	}
	
	/**
	 * 是否存在
	 * @return
	 */
	public boolean has(){
		count("*");
		int result = unique(Integer.class);
		return result > 0;
	}
	
	public M in(String name,Object...params){
		add(" "+name+" in(?");
		for (int i = 1; i < params.length; i++)
			add(",?");
		add(")",params);
		return self();
	}
	
	public M in(String name,String conditions){
		add(" "+name+" in ("+conditions+")");
		return self();
	}
	
	
	/**
	 * 删除
	 * @param sql
	 * @param objects
	 * @return
	 */
	public int delete(String sql,Object...objects){
		int result = db().update(sql, objects);
		return result;
	}
	
	/**
	 * 删除
	 * @return
	 */
	public int delete(){
		StringBuilder sql = new StringBuilder();
		sql.append("delete from ");
		sql.append(tableName());
		sql.append(sqlHelper.getSqlBulider());
		return delete(sql.toString(),sqlHelper.getParamArray());
	}
	
	/**
	 * 删除
	 * @param id
	 * @return
	 */
	public int delete(Id id){
		where(pk()+" = ?",id);
		return delete();
	}
	
	/**
	 * 删除
	 * @param id
	 * @return
	 */
	public int deleteAll(Id...id){
		StringBuilder idsts = new StringBuilder();
		for (int i = 0; i < id.length; i++) {
			if(i == 0)
				idsts.append("?");
			else
				idsts.append(",?");
		}
		where(pk()+" in ("+idsts.toString()+")", id);
		return delete();
	}
	
	
	/**
	 * 更新
	 * @return
	 */
	public int update(){
		StringBuilder sql = new StringBuilder();
		sql.append("update ");
		sql.append(tableName());
		sql.append(" set ");
		int size = sqlHelper.getColumns().size();
		sql.append(sqlHelper.getColumns().get(0)+" = ?");
		for (int i = 1; i < size; i++)
			sql.append(","+sqlHelper.getColumns().get(i)+" = ?");
		sql.append(sqlHelper.getSqlBulider());
		return db().update(sql.toString(),sqlHelper.getParamArray());
	}
	
	/**
	 * 更新
	 * @param id
	 * @return
	 */
	public int update(Id id){
		where(pk()+"= ?", id);
		return update();
	}
	
	public int updateAll(Id...idarr){
		String sql = pk()+" in (?";
		for (int i = 1; i < idarr.length; i++)
			sql +=",?";
		sql += ")";
		where(sql, idarr);
		return update();
	}

	/**
	 * 返回list
	 * @return
	 */
	public List<M> list(){
		return list(createQuery(), null, null, sqlHelper.getParams());
	}
	
	/**
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<M> list(String sql,List<Object> params){
		return list(new StringBuilder(sql), null, null, params);
	}
	
	/**
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<M> list(String sql,Object...params){
		List<Object> list = new ArrayList<Object>();
		for (int i = 0; i < params.length; i++)
			list.add(params[i]);
		return list(sql, list);
	}
	
	public List<M> list(String[] sqls,Object...params){
		String sql = "";
		for (int i = 0; i < sqls.length; i++)
			sql +=sqls[i];
		return list(sql, params);
	}
	
	/**
	 * 返回list
	 * @param sql
	 * @param page
	 * @param size
	 * @param params
	 * @return 
	 */
	@SuppressWarnings("rawtypes")
	public List<M> list(StringBuilder sql,Integer page,Integer size,List<Object> params){
		List<Object> param = new ArrayList<Object>(params);
		if(page != null && size != null){
			sql.append(" limit ? , ? ");
			param.add((page - 1)*size);
			param.add(size);
		}
		List<Map<String,Object>> results = db().queryForList(sql.toString(),param.toArray());
		List<M> models = new ArrayList<M>();
		for (Map<String, Object> m : results){
			Model model = (Model)createModel(modelClass());
			model.putAll(m);
			models.add((M) model);
		}
		return models;
	}
	
	/**
	 * @param page
	 * @param size
	 * @return
	 */
	public List<M> list(int page,int size){
		return list(createQuery(),page,size,sqlHelper.getParams());
	}
	
	
	/**
	 * 创建一个普通 model 对象
	 * 不具备 sql 执行功能
	 * @return
	 */
	protected static <M> M createModel(Class<M> modelClass ){
		return BeanUtils.instantiate(modelClass);
	}
	
	/**
	 * 分页
	 * @param page
	 * @param size
	 * @return
	 */
	public Page<M> page(int page,int size){
		Page<M> p = new Page<M>();
		p.setPage(page);
		p.setSize(size);
		p.setData(list(page, size));
		p.setTotal(total());
		return p;
	}
	
	/**
	 * 返回唯一结果
	 * @param requiredType
	 * @return
	 */
	public <T> T unique(Class<T> requiredType){
		return Converter.convert(requiredType,db().queryForObject(createQuery().toString(),requiredType,sqlHelper.getParams().toArray()).toString());
	}
	
	/**
	 * 返回一个model
	 * @return
	 */
	public M find(){
		List<M> models = list();
		if(models.isEmpty())
			return null;
		return models.get(0);
	}
	
	public M find(Id id){
		where();
		eq(pk(), id);
		return find();
	}
	
	/**
	 * 第一条数据
	 * @return
	 */
	public M first(){
		List<M> models = list(1, 1);
		if(!models.isEmpty())
			return models.get(0);
		return null;
	}
	
	private StringBuilder createQuery(){
		StringBuilder sql = new StringBuilder();
		sql.append("select ");
		int size = sqlHelper.getColumns().size();
		if(size == 0){
			sql.append("*");
		}else{
			sql.append(sqlHelper.getColumns().get(0));
			for (int j = 1; j < size; j++)
				sql.append(","+ sqlHelper.getColumns().get(j));
		}
		sql.append(" from ");
		sql.append(tableName());
		sql.append(" ");
		sql.append(sqlHelper.getSqlBulider());
		return sql;
	}

	
	private String createInsert(){
		StringBuilder sql = new StringBuilder();
		int s = sqlHelper.getColumns().size();
		sql.append("insert into "+tableName()).append(" ("+sqlHelper.getColumns().get(0));
		for (int i = 1; i <s ; i++)
			sql.append(","+sqlHelper.getColumns().get(i));
		sql.append(") values (?");
		for (int i = 1; i < s; i++)
			sql.append(",?");
		sql.append(")");
		return sql.toString();
	}
	/**
	 * 保存
	 * save
	 * @return
	 */
	public Id save(){
		if(isGenerarerId()){
			GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
			PreparedStatementCreator creator = new GenerarerIdSaveCreator(createInsert(),sqlHelper.getParamArray());
			db().update(creator,keyHolder);
			return Converter.convert(idClass(),keyHolder.getKey().toString());
		}else{
			db().update(createInsert(),sqlHelper.getParamArray());
		}
		 return null;
	}

	/**
	 * 表名 默认获取 
	 * table name
	 * @return
	 */
	protected String tableName(){
		return modelClass().getSimpleName().toLowerCase();
	}
	
	
	/**
	 * 主键名称
	 * primary key name
	 * @return
	 */
	protected  String pk() {
		return "id";
	}
	
	/**
	 * 获取id
	 * @return
	 */
	public final Id keyId(){
		return get(pk());
	}
	
	
	/**
	 * 主键类型
	 * @return
	 */
	protected Class<Id> idClass(){
		return (Class<Id>) (((ParameterizedType) this.getClass().getGenericSuperclass()).getActualTypeArguments()[1]);
	}
	
	/**
	 * model class
	 * @return
	 */
	protected Class<M> modelClass(){
		return (Class<M>) (((ParameterizedType) this.getClass().getGenericSuperclass()).getActualTypeArguments()[0]);
	}

	public boolean isGenerarerId() {
		return generarerId;
	}

	public void setGenerarerId(boolean generarerId) {
		this.generarerId = generarerId;
	}
}
